USE N'<Database Name, NVARCHAR(MAX), >' 
GO

/****** Object:  StoredProcedure [dbo].[ExtendedProperties_Object]    Script Date: 2/5/2015 10:30:03 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



/******************************************************************************************
Procedure Name: ExtendedProperties_Object

Purpose: Adds/Updates extended property information.  This is the base function called by all the helper functions.

Parameters: 
	@value		sql_variant	 Is the value associated with the property. value is sql_variant, with a default of NULL. The size of value may not be more than 7,500 bytes.
	@level0type	varchar(128)	 Is the user or user-defined type. level0_object_type is varchar(128), with a default of NULL. Valid inputs are ASSEMBLY, CONTRACT, EVENT NOTIFICATION, FILEGROUP, MESSAGE TYPE, PARTITION FUNCTION, PARTITION SCHEME, REMOTE SERVICE BINDING, ROUTE, SCHEMA, SERVICE, USER, TRIGGER, TYPE, and NULL.
	@level0name	sysname		 Is the name of the level 1 object type specified. level0_object_name is sysname with a default of NULL.
	@level1type	varchar(128)	 Is the type of level 1 object. level1_object_type is varchar(128) with a default of NULL. Valid inputs are AGGREGATE, DEFAULT, FUNCTION, LOGICAL FILE NAME, PROCEDURE, QUEUE, RULE, SYNONYM, TABLE, TYPE, VIEW, XML SCHEMA COLLECTION, and NULL.
	@level1name	sysname		 Is the name of the level 1 object type specified. level1_object_name is sysname with a default of NULL.
	@level2type	varchar(128)	 Default = NULL.  Is the type of level 2 object. level2_object_type is varchar(128) with a default of NULL. Valid inputs are COLUMN, CONSTRAINT, EVENT NOTIFICATION, INDEX, PARAMETER, TRIGGER, and NULL.
	@level2name	sysname		 Default = NULL.  Is the name of the level 2 object type specified. level2_object_name is sysname, with a default of NULL.
  
History:
    09/03/2014	 AkersMa	   Initial release  
******************************************************************************************/



CREATE PROCEDURE [dbo].[ExtendedProperties_Object] (
	@value	sql_variant,
	@level0type	varchar(128),
	@level0name	sysname,	  
	@level1type	varchar(128),
	@level1name	sysname,
	@level2type	varchar(128) = NULL,
	@level2name	sysname = NULL
)
AS
-- set default name for object documentation
DECLARE @name sysname; SELECT @name = N'MS_Description';

IF EXISTS (
		SELECT value FROM fn_listextendedproperty(
			@name, 
			@level0type, @level0name, 
			@level1type, @level1name,
			@level2type, @level2name
			)
	)
	BEGIN
		-- update
		EXEC sys.sp_updateextendedproperty 
			@name, 
			@value, 
			@level0type, @level0name, 
			@level1type, @level1name,
			@level2type, @level2name

	END
ELSE
	BEGIN
		-- add
		EXEC sys.sp_addextendedproperty 
			@name, 
			@value, 
			@level0type, @level0name, 
			@level1type, @level1name,
			@level2type, @level2name
	END

RETURN (0)



GO


